#!/usr/bin/python

# Imports
import pygame, os, sys
from pygame import string
import MySQLdb,  _mysql_exceptions
from conf import db_host, db_user, db_passwd

class SQL(object):
    """Contains functions to create, update, select or check things through the database."""
    def __init__(self):
        """Initialization of SQL."""
        print 'Initializing SQL'
        self.connection = False
    
    def get_closest_shape_diffs(self, shape_diff, face_ids):
        """Returns the user_ids of the users that have the closest shape_diff compared to the incoming shape_diff
        and where the face_id(s) are equal to the incoming once.
        Parameters: float shape_diff - return value of faceDetect.matchShapes()
                    list face_ids - list of face_id's of users_faces."""
        query = []
        query.append("SELECT user_id FROM test_db.users_faces WHERE")
        for face_id in face_ids:
            query.append(" face_id = %s OR" % face_id)
        last_face_id = query[len(query) - 1]
        last_face_id = last_face_id[0:-2]
        query.remove(query[len(query) - 1])
        query.append(last_face_id)
        query.append("ORDER BY ABS(%s - shape_diff) LIMIT 5" % shape_diff)
        query = string.join(query,"")
        
        print 'Getting user_ids of closest shape_diff where face_ids = face_ids:',  query
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            return rows
        except Exception,  info:
            print "Error - Getting user_ids of closest shape_diff where face_ids = face_ids:",  info
    
    def get_closest_temp_diffs(self, temp_diff, face_ids):
        """Returns the user_ids of the users that have the closest temp_diff compared to the incoming temp_diff
        and where the face_id(s) are equal to the incoming once.
        Parameters: float shape_diff - return value of faceDetect.matchTemplate()
                    list face_ids - list of face_id's of users_faces."""
        query = []
        query.append("SELECT user_id FROM test_db.users_faces WHERE")
        for face_id in face_ids:
            query.append(" face_id = %s OR" % face_id)
        last_face_id = query[len(query) - 1]
        last_face_id = last_face_id[0:-2]
        query.remove(query[len(query) - 1])
        query.append(last_face_id)
        query.append("ORDER BY ABS(%s - temp_diff) LIMIT 5" % temp_diff)
        query = string.join(query,"")
            
        print 'Getting user_ids of closest temp_diff where face_ids = face_ids:',  query
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            return rows
        except Exception, info:
            print "Error - Getting user_ids of closest temp_diff where face_ids = face_ids:",  info
    
    def get_oldest_face_id(self, user_id):
        """Returns the oldest face_id of an user.
        Parameters: int user_id - user identification code"""
        query = "SELECT face_id FROM test_db.users_faces WHERE user_id = %s AND datetime = (SELECT MIN(datetime) FROM test_db.users_faces)" % user_id
        print "Getting the oldest face_id of an user:",  query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchall()
            return row[0][0]
        except Exception,  info:
            print 'Error - Getting the oldest face_id of an user:',  info
    
    def get_most_recent_faces(self, user_id):
        """Returns the three most recent face_ids of an user.
        Parameters: int user_id - user identification code"""
        query = "SELECT face_id FROM test_db.users_faces WHERE user_id = %s ORDER BY datetime DESC LIMIT 3" % (user_id)
        print 'Getting three most recent faces:',  query
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            return rows
        except Exception,  info:
            print 'Error - Getting three most recent faces:', info
    
    def get_pic_name(self, face_id):
        """Returns the picture name of the given face_id.
        Parameters: int face_id - user_face identication code"""
        query = "SELECT pic_name FROM test_db.users_faces WHERE face_id = %s" % face_id
        print "Getting the pic_name of a face_id:", query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            return row[0]
        except Exception,  info:
            print "Error - Getting the pic_name of a face_id:", info
    
    def get_user(self, user_id = None, login_name = None):
        """Returns the user's user_id, title, first name and last name. Searches by user_id.
        Parameters: int user_id - user identification code
                    string login_name - login name of an user
        Either of one should always be None."""
        if login_name:
            query = "SELECT user_id, title, first_name, last_name FROM test_db.users WHERE login_name = '%s'" % login_name
        elif user_id:
            query = "SELECT user_id, title, first_name, last_name FROM test_db.users WHERE user_id = %s" % user_id
        print "Getting user:",  query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            return row
        except Exception,  info:
            print "Error - Getting user:",  info
    
    def get_user_face(self, pic_name):
        """Returns the face_id of a pic_name.
        Parameters: string pic_name - picture name
        Only used in conf.py"""
        query = "SELECT face_id FROM test_db.users_faces WHERE pic_name = '%s'" % pic_name
        print 'Getting user_face:', query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            return row[0]
        except Exception, info:
            print 'Error - Getting user_face:', info
    
    def get_all_users(self):
        """Returns the title and name of all users."""
        query = "SELECT user_id, title, first_name, last_name FROM test_db.users ORDER BY last_name"
        print "Get all users:",  query
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            return rows
        except Exception,  info:
            print "Error - Get all users:",  info
    
    def get_user_list(self):
        """Returns all users excluding the test_users.
        Only created for prototype to avoid showing all the test users."""
        query = "SELECT user_id, title, first_name, last_name FROM test_db.users WHERE first_name != 'User'"
        print "Get user list:",  query
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            return rows
        except Exception,  info:
            print "Error - Get user list:",  info
        
    def get_last_inserted_user_id(self):
        """Returns the user_id of the last inserted user."""
        query = "SELECT user_id FROM test_db.users ORDER BY user_id DESC LIMIT 1"
        print 'Getting last inserted user_id:', query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            return row[0]
        except Exception, info:
            print 'Error - Getting last inserted user_id:', info
    
    def get_max_user_id(self):
        """Returns the highest user_id."""
        query = "SELECT MAX(user_id) FROM test_db.users"
        print 'Getting the highest user_id', query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            return row[0]
        except Exception,  info:
            print 'Error - Getting the highest user_id',  info
    
    def user_exists(self, user_id = None, login_name = None):
        """Checks if the given user_id OR login_name exists.
        Parameters: int user_id - user identification code
                    string login_name - login name of an user
        Either of one should always be None."""
        query = ''
        if login_name:
            query = "SELECT * FROM test_db.users WHERE login_name = '%s'" % (login_name)
        elif user_id:
            query = "SELECT * FROM test_db.users WHERE user_id = %s" % (user_id)
        print "User exists:",  query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            if row == None:
                return False
            return True
        except Exception,  info:
            print "Error - User exists:",  info
    
    def user_face_exists(self, pic_name):
        """Checks if the given pic_name exists.
        Parameters: string pic_name - picture name
        Only used in conf.py"""
        query = "SELECT * FROM test_db.users_faces WHERE pic_name = '%s'" % pic_name
        print 'User_face exists:', query
        try:
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            if row == None:
                return False
            return True
        except Expcetion,  info:
            print "Error - User_face exists:", info
    
    def count_users_faces(self, user_id):
        """Return the amount of users_faces.
        Parameters: int user_id - user identification code"""
        query = "SELECT count(*) FROM test_db.users_faces WHERE user_id = %s" % user_id
        print "Count users_faces:",  query
        try: 
            self.cursor.execute(query)
            row = self.cursor.fetchone()
            return row[0]
        except Exception,  info:
            print "Error - Count users_faces:",  info
    
    def update_user_face(self, shape_diff, temp_diff, pic_name, face_id):
        """Updates an user_face record.
        Parameters: float shape_diff - return value of faceDetect.matchShapes()
                    float temp_diff - return value of faceDetect.matchTemplate()
                    string pic_name - picture name
                    int face_id - user_face identication code"""
        query = '''UPDATE test_db.users_faces SET shape_diff = %s, 
                                                                        temp_diff = %s,
                                                                        pic_name = '%s',
                                                                        datetime = NOW() WHERE face_id = %s''' % (shape_diff, temp_diff, pic_name, face_id)
        print "Updating user_face: face_id =", face_id, 'VALUES(', shape_diff, ',',  temp_diff, ')'
        try:
            self.cursor.execute(query)
        except Exception,  info:
            print 'Error - Updating user_face',  info
    
    def create_user_face(self, shape_diff, temp_diff, pic_name, user_id):
        """Creates an user_face record.
        Parameters: float shape_diff - return value of faceDetect.matchShapes()
                    float temp_diff - return value of faceDetect.matchTemplate()
                    string pic_name - picture name
                    int user_Id - user identication code"""
        print "PICNAME: ",  pic_name
        query = '''INSERT INTO test_db.users_faces (user_id, shape_diff, temp_diff, pic_name, datetime) 
                                    VALUES(%s, %s, %s, '%s', NOW())''' % (user_id,  shape_diff, temp_diff, pic_name)
        print "Creating user_face: user_id =", user_id, 'VALUES(', shape_diff, ',', temp_diff, ')'
        try:
            self.cursor.execute(query)
        except Exception,  info:
            print 'Error - Creating user_face', info
    
    def create_user(self, name):
        """Creates an user.
        Parameters: string name - name of the user (format: 'Piet' or 'Piet Hein')"""
        query = ''
        user_id = 1
        if self.get_max_user_id():
            user_id = self.get_max_user_id() + 1
        if " " in name:
            first_name, last_name = name.split(" ")
            first_name, last_name = first_name.capitalize(), last_name.capitalize()
            full_name = first_name + '_' + last_name
            query = "INSERT INTO test_db.users VALUES (%s, '%s', 'Dhr.', '%s', '%s', NOW(), 'BT+ Group', '', '', '', 50)" % (user_id, full_name, first_name, last_name)
        else:
            query = "INSERT INTO test_db.users VALUES (%s, '%s_Achternaam', 'Dhr.', '%s', 'Achternaam', NOW(), 'BT+ Group', '', '', '', 50)" % (user_id, name,  name)
        print "Creating an user:",  query
        try:
            self.cursor.execute(query)
        except Exception,  info:
            print "Error - Creating an user:",  info

    def connect_db(self):
        """Creates a connection with the database.
        For the database configuration see conf.py."""
        print 'Made connection with %s' % db_host
        self.connection = True
        self.conn = MySQLdb.connect(host = db_host, user = db_user, passwd = db_passwd)
        self.cursor = self.conn.cursor()
    
    def close_db(self):
        """Closes the connection with the database."""
        print 'Closing database connection'
        self.connection = False
        self.conn.close()
